package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



import modellayer.Supplier;


/**
 * The customerDB class to store customer information
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */


public class SupplierDB implements IFSupplierDB {

	
	
	private Connection con;
	private boolean successCheck;
	
	/**
	 * SupplierDB Fields
	 * @param con
	 * @param succes
	 */
	
	
	/**
	 * Constructor for the SupplierDB class
	 */
	public SupplierDB() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	
	/**
	 * Insert method to get Suppleir information in to the database
	 * @param supplierObj
	 */
	@Override
	public int insertSupplier(Supplier supplierObj) throws Exception {
		int rc = -1;
		
		String query = "INSERT INTO Supplier (name, address, country, phonenumberOriginCode, phonenumber, email) VALUES ('"+supplierObj.getName()+"', '"+supplierObj.getAddress() + "', '" + supplierObj.getCountry() + "', '" + supplierObj.getPhonenumberOriginCode() + "', '" + supplierObj.getPhonenumber() + "', '" + supplierObj.getEmail() + "');";
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch(SQLException ex) {
			System.out.println("SQL query:  " + query);
			System.out.println("    error:  " + ex);			
			throw new Exception("Failed to insert Supplier into the Database!");		
		}		
		return rc;		
	}
	
	/**
	 * delete method to remove suppliers from the database
	 * @param supplierId
	 */
	@Override
	public boolean deleteSupplier(int supplierId)
	{
		successCheck = false;
		
		String query = "DELETE FROM Supplier WHERE supplierId='"+supplierId+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);			
			stmt.close();
			successCheck = true;
		} 
		catch (Exception ex) 
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return successCheck;
	}
	
	/**
	 * update method to update supplier information in the database
	 * @param supplierId
	 * @param name
	 * @param address
	 * @param country
	 * @param phonenumberOriginCode
	 * @param phonenumber
	 * @param email
	 */
	
	@Override
	public boolean updateSupplier(int supplierId, String name, String address, String country, String phonenumberOriginCode, String phonenumber, String email)
	{
		successCheck = false;
		
		String query = "UPDATE Supplier " + "SET name='"+name+"', address='"+address+"', country='"+country+"', phonenumberOriginCode='"+phonenumberOriginCode+"', phonenumber='"+phonenumber+"', email='"+email+"' " + "WHERE supplierId='"+supplierId+"'";


		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
			successCheck = true;
		}
		catch (SQLException ex)
		{
			System.out.println("SQL query" +query);
			System.out.println("Error: " +ex);
		}
		
		return successCheck;
	}
	
	/**
	 * search method to find suppliers in the database
	 * @param supplierId
	 */
	@Override
	public Supplier findSupplier(int supplierId) {
		String wClause = " supplierId='"+supplierId+"'";
		return singleWhere(wClause);
	}

	/**
	 * search method to find suppliers in the database
	 * @param name
	 */
	@Override
	public Supplier findSupplier(String name) {
		String wClause = " name='"+name+"'";
		return singleWhere(wClause);
	}

	/**
	 * singleWhere method to execute a method in the database
	 * @param wClause
	 * @return supplierObj
	 */
	private Supplier singleWhere(String wClause) {
		ResultSet results;
		Supplier supplierObj = null;
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if (results.next()) {
				supplierObj = buildSupplier(results);
			} else {
				supplierObj = null;
			}
			
			stmt.close();
		} catch (Exception ex) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return supplierObj;
	}

	/**
	 * build query method to create the query
	 * @param wClause
	 * @return query
	 */
	
	private String buildQuery(String wClause) {
		String query = "SELECT supplierId, name, country, phonenumberOriginCode, phoneNumber, email FROM Supplier";
		
		if (!wClause.isEmpty()) {
			query += " WHERE " + wClause;
		}
		
		return query;
	}
	
	/**
	 * create method to create a supplier from the database
	 * @param result
	 * @return supplierObj
	 */
	private Supplier buildSupplier(ResultSet result) {
		Supplier supplierObj = null;
		try {
			supplierObj = new Supplier(result.getInt("supplierId"), result.getString("name"), result.getString("address"), result.getString("country"), result.getString("phonenumberOriginCode"), result.getString("phonenumber"), result.getString("email"));
		} catch (SQLException e) {
			System.out.println("Error building Supplier object!\nError: " + e);
		}
		
		return supplierObj;
	}
	
	
	
}
